package fcf.cat;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import com.mysql.jdbc.DatabaseMetaData;

public class DBase {
	
	  /**
	   * 
	   * @String db_connect_str
	   * @param db_userid
	   * @param db_password
	   * @return
	   */
	  public static Connection connect(String db_connect_str,String db_userid, String db_password) {
		  
	        Connection conn;
	        try {
	            Class.forName("com.mysql.jdbc.Driver").newInstance();
	            conn = DriverManager.getConnection(db_connect_str,db_userid,db_password);
	        } catch(Exception e) {
	        	Utils.linea(e.getMessage());
	            e.printStackTrace();
	            conn = null;
	        }
	        return conn;
	        
      }
	  
	  /**
	  * Ejecuta query deseada
	  * @String DB - nombre de la base de datos 
	  * @param query - a ejecutar
	  * @return 
	  */
	  public static boolean query(String DB,String query){

		  	Connection conn = connect(Config.BD_SERVER_NODB+DB,Config.BD_USER,Config.BD_PASS);
		    Statement stmt;
	
		    try {
		    	Utils.linea(query);
		     	stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
		       	stmt.executeUpdate(query);
		       	return true;
		    }catch(Exception e){
		    	Utils.linea(e.getMessage());
		    }
			
		    return false;
		    
	   }
	    
	    /**
	    * Funcion para ejecutar bucle de consultas,se le pasa la conexion
	    * @Connection conn = DBase.connect(Config.BD_SERVER_DB,Config.BD_USER,Config.BD_PASS);
	    * @String DB - nombre de la base de datos 
	    * @param query - a ejecutar
	    * @return 
	    */
	    public static boolean multi_query(Connection conn,String DB,String query){
	    	
	        Statement stmt;

	        try {
	        	stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	        	stmt.executeUpdate(query);
	        	return true;
	        }catch(Exception e){
	        	Utils.linea(e.getMessage());
	        }
			return false;
			
	    }
	    
	    /**
	    * Ejecuta query deseada
	    * @String DB - nombre de la base de datos 
	    * @param query - a ejecutar
	    * @return 
	    */
	    public static ResultSet select(String DB,String query){
	    	
	    	Connection conn = connect(Config.BD_SERVER_NODB+DB,Config.BD_USER,Config.BD_PASS);
	        Statement stmt;

	        try {
	        	stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	        	return stmt.executeQuery(query);
	        }catch(Exception e){
	        	Utils.linea(e.getMessage());
	        }
			return null;
			
	    }  
    
	    /**
	     * Realiza exportacion de los datos de una tabla a un csv
	     * @String tabla - nombre de la tabla
	     * @String fichero - nombre del fichero.csv
	     * @String splitBy - caracter usado para separar campos en el csv
	     * @String newLine - caracter usado para el salto de linea en el csv
	     */
	    public static void exportData(String tabla,String fichero,String splitBy,String newLine) {
	        
	        Connection conn = connect(Config.BD_SERVER_NODB,Config.BD_USER,Config.BD_PASS);
	        Statement stmt;
	        String query;
	        
	        try {
	            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	            
	            String fields_terminated = "FIELDS TERMINATED BY '"+splitBy+"' OPTIONALLY ENCLOSED BY '\\\"' ";
	            String lines_terminated = "LINES TERMINATED BY '"+newLine+"'";
	            
	            query = "SELECT * INTO OUTFILE '"+Config.CSV_FOLDER+fichero+"' CHARACTER SET utf8 "+fields_terminated+"  "+lines_terminated+" FROM "+tabla+";";
	            System.out.println(query);
	            stmt.executeQuery(query);
	            Utils.linea("DATOS EXPORTADOS CORRECTAMENTE A => "+Config.CSV_FOLDER+fichero);
	        } catch(Exception e) {
	        	Utils.linea(e.getMessage());
	            e.printStackTrace();
	            stmt = null;
	        }
	        
	    }
	    
	    
	    /**
	     * Realiza exportacion de los datos de una tabla a un csv
	     * @String tabla - nombre de la tabla
	     * @String fichero - nombre del fichero.csv
	     * @String splitBy - caracter usado para separar campos en el csv
	     * @String newLine - caracter usado para el salto de linea en el csv
	     */
	    public static void exportDataQuery(String queryString,String fichero,String splitBy,String newLine) {
	    	
	    	String fields_terminated = "FIELDS TERMINATED BY '"+splitBy+"' OPTIONALLY ENCLOSED BY '\\\"' ";
            String lines_terminated = "LINES TERMINATED BY '"+newLine+"'";
	    	System.out.println(queryString+" INTO OUTFILE '"+Config.CSV_FOLDER+fichero+"' CHARACTER SET utf8 "+fields_terminated+"  "+lines_terminated);
	    	/*
	        Connection conn = connect(Config.BD_FFCATALUNYA,Config.BD_USER,Config.BD_PASS);
	        Statement stmt;
	        String query;
	        
	        try {
	            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	            
	            String fields_terminated = "FIELDS TERMINATED BY '"+splitBy+"' OPTIONALLY ENCLOSED BY '\\\"' ";
	            String lines_terminated = "LINES TERMINATED BY '"+newLine+"'";
	            System.out.println(queryString);
	            query = queryString+" INTO OUTFILE '"+Config.CSV_FOLDER+fichero+"' CHARACTER SET utf8 "+fields_terminated+"  "+lines_terminated;
	            
	            stmt.executeUpdate(query);
	            Utils.linea("DATOS EXPORTADOS CORRECTAMENTE A => "+Config.CSV_FOLDER+fichero);
	        } catch(Exception e) {
	        	Utils.linea(e.getMessage());
	            e.printStackTrace();
	            stmt = null;
	        }
	        */
	    }
    
	    /**
	     * 
	     * @param tabla
	     * @param fichero
	     */
	    public static void importData(String tabla,String fichero,String splitBy,String newLine){
	    	
	    	 Connection conn = connect(Config.BD_SERVER,Config.BD_USER,Config.BD_PASS);
	         Statement stmt;
	         String query;
	         
	         try {
	             stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	             
	             String fields_terminated = "FIELDS TERMINATED BY '"+splitBy+"' OPTIONALLY ENCLOSED BY '\\\"' ";
	             String lines_terminated = "LINES TERMINATED BY '"+newLine+"'";
	             query = "LOAD DATA LOCAL INFILE '"+Config.CSV_FOLDER+fichero+"' INTO TABLE "+tabla+" CHARACTER SET utf8 "+fields_terminated+lines_terminated+";";
	             //System.out.println(query);
	             stmt.executeQuery(query);
	             System.out.println("***** Datos importados correctamente a ->"+tabla);
	         } catch(Exception e) {
	        	 Utils.linea(e.getMessage());
	        	 e.printStackTrace();
	             stmt = null;
	         }
	    }
	    
	    /**
	     * Comprueba si existe una tabla en la BBDD deseada, devuelve FALSE o TRUE
	     * @String DB - 
	     * @String tabla -
	     * @return
	     * @throws SQLException
	     */
	    public static boolean existeTabla(String DB,String tabla) throws SQLException{
	    	
	    	Connection conn = connect(Config.BD_SERVER_NODB+DB,Config.BD_USER,Config.BD_PASS);
	    	
	    	java.sql.DatabaseMetaData dbm = (DatabaseMetaData) conn.getMetaData();
	
	    	ResultSet tables = dbm.getTables(null, null,tabla, null);
	    	
			if (tables.next()) {
				Utils.linea("TABLA =>"+DB+"."+tabla+": EXISTE");
				return true;
			}else {
				Utils.linea("TABLA =>"+DB+"."+tabla+": NO EXISTE");
				return false;
			}
	    }
  	    
	    /**
		* Realiza backup de una tabla 
		* Recibe string con la base de datos y la tabla 
		* @throws SQLException
		*/
		public static void backupTabla(String BBDD,String tabla,String BD_dest,String table_backup) throws SQLException{
			
			
			Utils.linea("INICIANDO BACKUP TABLA "+BBDD+"."+tabla);
			
			if(existeTabla(BBDD,tabla)){ // si existe la tabla que queremos realizar backup
				if(existeTabla(BD_dest,table_backup)){ //si ya existe la tabla backup la borramos para crear una nueva
					DBase.deleteBD(BD_dest,table_backup);
				}
				DBase.copiarEstructuraTabla(BBDD,tabla,BD_dest,table_backup); //copiamos estructura tabla recibida a una nueva tabla llamada igual acabada en _backup
				DBase.copiarDatosTabla(BBDD,tabla,BD_dest,table_backup); //copiamos datos de la tabla recibida al backup
				
				Utils.linea("FIN BACKUP TABLA "+tabla);
			}else{ //si no existe la tabla 
				Utils.linea("NO EXISTE TABLA =>"+BBDD+"."+tabla);
			}
			/**/
		}
		
		/**
		 * NO usar esta funcion directamente. llamar a la funcion backup - cambiar a copiarTabla
	     * Realiza una copia de la estructura de una tabla a otra
		 * @param BD - Base de datos origen desde donde copiamos la estructura de la tabla
		 * @param tabla - Nombre de la tabla de origen
		 * @param BD_dest - Base de datos donde copiaremos la estructura
		 * @param tabla_dest - Nombre de la tabla de destino
		 */
	    public static void copiarEstructuraTabla(String BD,String tabla,String BD_dest,String tabla_dest){
	    	
	    	
	    	tabla = BD+"."+tabla;
	    	tabla_dest = BD_dest+"."+tabla_dest;

	    	Connection conn = connect(Config.BD_SERVER_NODB+BD,Config.BD_USER,Config.BD_PASS);
	        Statement stmt;
	        String query = "CREATE TABLE "+tabla_dest+" LIKE "+tabla+";";
	        System.out.println(query);
	        try {
	        	stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	        	stmt.executeUpdate(query);
	        	Utils.linea("ESTRUCTURA TABLA ->"+tabla+" COPIADA CORRECTAMENTE A ->"+tabla_dest);
	        }catch(Exception e){
	        	Utils.linea(e.getMessage());
	        }
	    }
	    
	    /**
	     * NO usar esta funcion directamente. llamar a la funcion backup
	     * Copia los datos una table en la base de datos por defecto 
	     * @param tabla
	     */
	    public static void copiarDatosTabla(String BD,String tabla,String BD_dest,String tabla_dest){
	    	
	    	tabla = BD+"."+tabla;
	    	tabla_dest = BD_dest+"."+tabla_dest;
	    	
	    	Connection conn = connect(Config.BD_SERVER,Config.BD_USER,Config.BD_PASS);
	        Statement stmt;
	        String query = "INSERT INTO "+tabla_dest+" SELECT * FROM "+tabla+";";
	        System.out.println(query);
	        try {
	        	stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	        	stmt.executeUpdate(query);
	        	Utils.linea("DATOS TABLA ->"+tabla+" COPIADOS CORRECTAMENTE A ->"+tabla_dest);
	        }catch(Exception e){
	        	Utils.linea(e.getMessage());
	        }
	        
	    }

	    /**
	     * Elimina la tabla de la BBDD deseada
	     * @String DB - Base de datos donde esta la tabla deseada
	     * @String tabla - Nombre de la tabla a eliminar
	     */
	    public static void deleteBD(String DB,String tabla){
	    	
	    	Connection conn = connect(Config.BD_SERVER_NODB+DB,Config.BD_USER,Config.BD_PASS);
	        Statement stmt;
	        String query = "DROP TABLE "+tabla;
	        
	        try {
	        	stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	        	stmt.executeUpdate(query);
	        	Utils.linea("DROP ->"+tabla+" REALIZADO CORRECTAMENTE");
	        }catch(Exception e){
	        	Utils.linea(e.getMessage());
	        }
	        
	    }
    
	    /**
	     * Realiza un borrado de la tabla deseada
	     * @String DB - Base de datos donde esta la tabla
	     * @String tabla - Nombre de la tabla que deseamos eliminar contenido
	     */
	    public static void truncateDB(String DB,String tabla){
	    	
	    	Connection conn = connect(Config.BD_SERVER_NODB+DB,Config.BD_USER,Config.BD_PASS);
	        Statement stmt;
	        String query = "TRUNCATE TABLE "+DB+"."+tabla;
	        
	        try {
	        	stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
	        	stmt.executeUpdate(query);
	        	Utils.linea("TRUNCATE ->"+tabla+" REALIZADO CORRECTAMENTE");
	        }catch(Exception e){
	        	Utils.linea(e.getMessage());
	        }
	        
	    }
	    
    
};